package com.sgcc.manager.sql.dao.impl;

import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCountCallbackHandler;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import com.sgcc.manager.sql.biz.impl.SessionDBUtil;
import com.sgcc.manager.sql.dao.ISqlDao;
import com.sgcc.manager.sql.util.SqlClassUtil;
import com.sgcc.manager.sql.vo.JdbcTemplateUtil;
@Repository("SqlDaoImpl")
public class SqlDaoImpl implements ISqlDao {
	Logger logger=Logger.getLogger(this.getClass().getName());
	public void execute(String sessionId,String sql)throws Exception {
		SessionDBUtil.get(sessionId).getJdbcTemplate().execute(sql);
	}
	public List queryList(String sessionId,String sql)throws Exception {
		RowCountCallbackHandler rcch = new RowCountCallbackHandler();
		SessionDBUtil.get(sessionId).getJdbcTemplate().query(sql, rcch);
		String[] coloumnName = rcch.getColumnNames();
		int[] coloumnType = rcch.getColumnTypes();
		return SessionDBUtil.get(sessionId).getJdbcTemplate().queryForList(sql);
	}
	public int update(String sessionId,String sql) throws Exception {
		return SessionDBUtil.get(sessionId).getJdbcTemplate().update(sql);
	}
	public SqlRowSet queryForRowSet(String sessionId,String sql,int currentPage,int pageSize) throws Exception {
		if(SqlClassUtil.isSEQUENCE(sql) || currentPage==-1){
			//String fieldNamesStr=SqlClassUtil.getFieldNameStr(sql);
		}else {
			String fieldNamesStr="*";
			//判断字段是否需要应用分页
			//int pageSize=20;
			//起止页数
			int bpos=(currentPage-1)*pageSize+1;
			int epos = bpos + pageSize;
			JdbcTemplateUtil jdbcDom=SessionDBUtil.get(sessionId);
			//分页处理
			StringBuffer sql2=new StringBuffer();
			
			if("mysql".equals(jdbcDom.getType())){
				sql2.append("select ").append(fieldNamesStr).append(" FROM ( ")
					.append(sql.toString()).append(" ) as temp LIMIT ")
					.append(Integer.toString(bpos)).append(" , ").append(Integer.toString(epos));
			}else{
				sql2.append(" SELECT ").append(fieldNamesStr).append(" FROM ").append(" (SELECT ROWNUM RN,TMPTBL.* FROM (")
					.append(sql.toString()).append(") TMPTBL WHERE ROWNUM <").append(Integer.toString(epos)).append(
							") WHERE RN >= ").append(Integer.toString(bpos));
			}
			sql = sql2.toString();
		}
		//分析出来字段
		return (SqlRowSet)SessionDBUtil.get(sessionId).getJdbcTemplate().query(sql, new SqlRowSetOracleResultSetExtractor());
		//return SessionDBUtil.get(sessionId).getJdbcTemplate().queryForRowSet(sql);
	}
	
	public SqlRowSet queryRowSet(String sessionId,String sql,int begin,int end) throws Exception {
			String fieldNamesStr="*";
			//判断字段是否需要应用分页
			//int pageSize=20;
			//起止页数
			int bpos=begin;
			int epos = end;
			JdbcTemplateUtil jdbcDom=SessionDBUtil.get(sessionId);
			//分页处理
			StringBuffer sql2=new StringBuffer();
			sql2.append(" SELECT ").append(fieldNamesStr).append(" FROM ").append(" (SELECT ROWNUM RN,TMPTBL.* FROM (")
				.append(sql.toString()).append(") TMPTBL WHERE ROWNUM <").append(Integer.toString(epos))
				.append(") WHERE RN >= ").append(Integer.toString(bpos));
			sql = sql2.toString();
		//分析出来字段
		return (SqlRowSet)SessionDBUtil.get(sessionId).getJdbcTemplate().query(sql, new SqlRowSetOracleResultSetExtractor());
		//return SessionDBUtil.get(sessionId).getJdbcTemplate().queryForRowSet(sql);
	}
	
	public int queryForCount(String sessionId,String sql)throws Exception {
		String fieldNamesStr="COUNT(*)";
		//判断字段是否需要应用分页
		//分页处理
		StringBuffer sql2=new StringBuffer();
		 sql2.append(" SELECT ").append(fieldNamesStr).append(" FROM ").append(" (")
				.append(sql.toString()).append(") a");
		sql = sql2.toString();
		//分析出来字段
		return SessionDBUtil.get(sessionId).getJdbcTemplate().queryForInt(sql);
	}
	public boolean LinkDB(String sessionId,String ipAdd, String db, String username,String password,String type) {
		BasicDataSource basicDataSource = new BasicDataSource(); 
		try{
			if("mysql".equals(type)){
				basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
				basicDataSource.setUrl("jdbc:mysql://"+ipAdd+":3306/"+db+"?useUnicode=true&characterEncoding=UTF-8");
				basicDataSource.setUsername(username);
				basicDataSource.setPassword(password); 
				JdbcTemplate jdbcTemplate = new JdbcTemplate(basicDataSource);
				JdbcTemplateUtil jdbcDom=new JdbcTemplateUtil(jdbcTemplate,type,username,password,db,ipAdd);
				SessionDBUtil.add(sessionId,jdbcDom);
				List list=SessionDBUtil.get(sessionId).getJdbcTemplate().queryForList("select now() from dual");
				return true;
			}else if("oracle".equals(type)) {
				basicDataSource.setDriverClassName("oracle.jdbc.OracleDriver");
				basicDataSource.setUrl("jdbc:oracle:thin:@"+ipAdd+":1521:"+db); 
				basicDataSource.setUsername(username);
				basicDataSource.setPassword(password); 
				JdbcTemplate jdbcTemplate = new JdbcTemplate(basicDataSource);
				JdbcTemplateUtil jdbcDom=new JdbcTemplateUtil(jdbcTemplate,type,username,password,db,ipAdd);
				SessionDBUtil.add(sessionId,jdbcDom);
				List list=SessionDBUtil.get(sessionId).getJdbcTemplate().queryForList("select * from dual");
				return true;
			}
			return false;
		}catch (Exception e) {
			logger.error("--------数据库连接出错--------");
			logger.error(e.toString());
			e.printStackTrace();
			return false;
		}
	}
	public boolean LinkDB(String sessionId,String oracleUrl, String username, String password,String type) {
		BasicDataSource basicDataSource = new BasicDataSource(); 
		try{
			if("oraDBs".equals(type)){
				basicDataSource.setDriverClassName("oracle.jdbc.OracleDriver");
				basicDataSource.setUrl(oracleUrl); 
				basicDataSource.setUsername(username);
				basicDataSource.setPassword(password); 
				System.out.println("集群oracleUrl----"+oracleUrl);
				JdbcTemplate jdbcTemplate = new JdbcTemplate(basicDataSource);
				JdbcTemplateUtil jdbcDom=new JdbcTemplateUtil(jdbcTemplate,type,username,password,oracleUrl);
				SessionDBUtil.add(sessionId,jdbcDom);
				List list=SessionDBUtil.get(sessionId).getJdbcTemplate().queryForList("select * from dual");
				return true;
			}else {
				return false;
			}
		}catch (Exception e) {
			logger.error("--------oraDBs集群连接出错--------");
			logger.error(e.toString());
			e.printStackTrace();
			return false;
		}
		
	}
}
